- frmBusFeePayment_StaffRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmBusFeePayment_StaffRecord
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Staff.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Staff.St_ID) as [ST ID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],RTRIM(BusCardHolder_Staff.Location) as [Location], RTRIM(BusFeePayment_Staff.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due] from Staff,BusFeePayment_Staff,BusCardHolder_Staff where BusCardHolder_Staff.BCH_ID=BusFeePayment_Staff.BusHolderID and BusCardHolder_Staff.StaffID=Staff.St_ID order by StaffName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Staff")
13 dgw.DataSource = ds.Tables("Staff").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Staff.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Staff.St_ID) as [ST ID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],RTRIM(BusCardHolder_Staff.Location) as [Location], RTRIM(BusFeePayment_Staff.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due] from Staff,BusFeePayment_Staff,BusCardHolder_Staff where BusCardHolder_Staff.BCH_ID=BusFeePayment_Staff.BusHolderID and BusCardHolder_Staff.StaffID=Staff.St_ID and StaffName like '" & txtStaffName.Text & "%' order by Staffname", con)
29 adp = New SqlDataAdapter(cmd)
30 ds = New DataSet()
31 adp.Fill(ds, "Staff")
32 dgw.DataSource = ds.Tables("Staff").DefaultView
33 con.Close()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
40 Try
41 con = New SqlConnection(cs)
42 con.Open()
43 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Staff.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Staff.St_ID) as [ST ID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],RTRIM(BusCardHolder_Staff.Location) as [Location], RTRIM(BusFeePayment_Staff.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due] from Staff,BusFeePayment_Staff,BusCardHolder_Staff where BusCardHolder_Staff.BCH_ID=BusFeePayment_Staff.BusHolderID and BusCardHolder_Staff.StaffID=Staff.St_ID and PaymentDate between @d1 and @d2 order by StaffName", con)
44 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
45 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
46 adp = New SqlDataAdapter(cmd)
47 ds = New DataSet()
48 adp.Fill(ds, "Staff")
49 dgw.DataSource = ds.Tables("Staff").DefaultView
50 con.Close()
51 Catch ex As Exception
52 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
53 End Try
54 End Sub
55
56
57 Sub Reset()
58 txtStaffName.Text = ""
59 dtpDateFrom.Text = Today
60 dtpDateTo.Text = Now
61 GetData()
62 End Sub
63 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
64 Reset()
65 End Sub
66
67 Private Sub frmStaffRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
68 GetData()
69 End Sub
70
71 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
72 Try
73 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
74 If lblSet.Text = "Bus Fee Payment" Then
75 Me.Hide()
76 frmBusFeePayment_Staff.Show()
77 frmBusFeePayment_Staff.cmbSession.DropDownStyle = ComboBoxStyle.DropDown
78 frmBusFeePayment_Staff.txtID.Text = dr.Cells(0).Value.ToString()
79 frmBusFeePayment_Staff.cmbInstallment.DropDownStyle = ComboBoxStyle.DropDown
80 frmBusFeePayment_Staff.txtBFPId.Text = dr.Cells(1).Value.ToString()
81 frmBusFeePayment_Staff.txtFeePaymentID.Text = dr.Cells(2).Value.ToString()
82 frmBusFeePayment_Staff.txtBusHolderID.Text = dr.Cells(3).Value.ToString()
83 frmBusFeePayment_Staff.txtSt_ID.Text = dr.Cells(4).Value.ToString()
84 frmBusFeePayment_Staff.txtStaffID.Text = dr.Cells(5).Value.ToString()
85 frmBusFeePayment_Staff.txtStaffName.Text = dr.Cells(6).Value.ToString()
86 frmBusFeePayment_Staff.txtDesignation.Text = dr.Cells(7).Value.ToString() '
87 frmBusFeePayment_Staff.txtLocation.Text = dr.Cells(8).Value.ToString()
88 frmBusFeePayment_Staff.cmbSession.Text = dr.Cells(9).Value.ToString()
89 frmBusFeePayment_Staff.cmbInstallment.Text = dr.Cells(10).Value.ToString()
90 frmBusFeePayment_Staff.txtBusFee.Text = dr.Cells(11).Value.ToString()
91 frmBusFeePayment_Staff.txtDiscountPer.Text = dr.Cells(12).Value.ToString()
92 frmBusFeePayment_Staff.txtDiscount.Text = dr.Cells(13).Value.ToString()
93 frmBusFeePayment_Staff.txtPreviousDue.Text = dr.Cells(14).Value.ToString()
94 frmBusFeePayment_Staff.txtFine.Text = dr.Cells(15).Value.ToString()
95 frmBusFeePayment_Staff.txtGrandTotal.Text = dr.Cells(16).Value.ToString()
96 frmBusFeePayment_Staff.txtTotalPaid.Text = dr.Cells(17).Value.ToString()
97 frmBusFeePayment_Staff.cmbPaymentMode.Text = dr.Cells(18).Value.ToString()
98 frmBusFeePayment_Staff.txtPaymentModeDetails.Text = dr.Cells(19).Value.ToString()
99 frmBusFeePayment_Staff.dtpPaymentDate.Text = dr.Cells(20).Value.ToString()
100 frmBusFeePayment_Staff.txtBalance.Text = dr.Cells(21).Value.ToString()
101 frmBusFeePayment_Staff.btnDelete.Enabled = True
102 frmBusFeePayment_Staff.cmbSession.Enabled = False
103 frmBusFeePayment_Staff.btnUpdate.Enabled = True
104 frmBusFeePayment_Staff.btnSave.Enabled = False
105 frmBusFeePayment_Staff.Button2.Enabled = False
106 frmBusFeePayment_Staff.dtpPaymentDate.Enabled = False
107 frmBusFeePayment_Staff.btnPrint.Enabled = True
108 frmBusFeePayment_Staff.cmbInstallment.Enabled = False
109 con = New SqlConnection(cs)
110 con.Open()
111 cmd = con.CreateCommand()
112 cmd.CommandText = "SELECT Session from BusFeePayment_Staff where ID=@d1"
113 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value)
114 rdr = cmd.ExecuteReader()
115 If rdr.Read() Then
116 frmBusFeePayment_Staff.cmbSession.Text = rdr.GetValue(0)
117 End If
118 If (rdr IsNot Nothing) Then
119 rdr.Close()
120 End If
121 If con.State = ConnectionState.Open Then
122 con.Close()
123 End If
124 lblSet.Text = ""
125 End If
126
127 Catch ex As Exception
128 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
129 End Try
130 End Sub
131
132 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
133 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
134 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
135 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
136 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
137 End If
138 Dim b As Brush = SystemBrushes.ControlText
139 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
140
141 End Sub
142
143 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
144 Dim rowsTotal, colsTotal As Short
145 Dim I, j, iC As Short
146 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
147 Dim xlApp As New Excel.Application
148 Try
149 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
150 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
151 xlApp.Visible = True
152
153 rowsTotal = dgw.RowCount
154 colsTotal = dgw.Columns.Count - 1
155 With excelWorksheet
156 .Cells.Select()
157 .Cells.Delete()
158 For iC = 0 To colsTotal
159 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
160 Next
161 For I = 0 To rowsTotal - 1
162 For j = 0 To colsTotal
163 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
164 Next j
165 Next I
166 .Rows("1:1").Font.FontStyle = "Bold"
167 .Rows("1:1").Font.Size = 12
168
169 .Cells.Columns.AutoFit()
170 .Cells.Select()
171 .Cells.EntireColumn.AutoFit()
172 .Cells(1, 1).Select()
173 End With
174 Catch ex As Exception
175 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
176 Finally
177 'RELEASE ALLOACTED RESOURCES
178 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
179 xlApp = Nothing
180 End Try
181 End Sub
182
183
184 End Class
